﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

/// <summary>
/// 文件名：ClsProductDataDAL.cs
/// 作  者：Kidy
/// 时  间：2006.11.20
/// 说  明：ClsProductDataDAL类用于商品资料信息相关处理
/// </summary>
public class ClsProductDataDAL
{
    /// <summary>
    /// 数据库连接字符串
    /// </summary>
    private static string strSqlConnectionString = ConfigurationManager.AppSettings["SQLCONNECTIONSTRING"];

    /// <summary>
    /// 获取所有的商品资料币别信息
    /// </summary>
    /// <param name="Co_ID">公司ID</param>
    /// <param name="User_ID">用户ID</param>
    /// <param name="Ds_ID">数据集ID</param>
    /// <returns>返回商品资料信息数据集</returns>
    public DataSet GetProductDatas(string Co_ID, string User_ID, string Ds_ID)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_getDataSetByProduct", con);//需改回来Pro_GetDataSetA

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;
        //dat.SelectCommand.Parameters.Add("@co_id", Co_ID);
        //dat.SelectCommand.Parameters.Add("@user_id", User_ID);
        //dat.SelectCommand.Parameters.Add("@ds_id", Ds_ID);

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 根据客户编号和当前登陆用户ID获取有效的商品信息(弹出窗口)
    /// </summary>
    /// <param name="Param">查询参数</param>
    /// <param name="Type">查询方式(0为精确,1为模糊)</param>
    /// <param name="CustomerNo">客户编号</param>
    /// <param name="UserID">当前登陆用户ID</param>
    /// <returns>返回有效的商品信息</returns>
    public DataSet GetProductDatas(string Param, int Type, string CustomerNo, string UserID)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetProductDatasToQuery", con);

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;
        dat.SelectCommand.Parameters.Add("@param", SqlDbType.NVarChar, 60);
        dat.SelectCommand.Parameters["@param"].Value = Param;
        dat.SelectCommand.Parameters.Add("@type", SqlDbType.Int, 4);
        dat.SelectCommand.Parameters["@type"].Value = Type;
        dat.SelectCommand.Parameters.Add("@cus_no", SqlDbType.NVarChar, 50);
        dat.SelectCommand.Parameters["@cus_no"].Value = CustomerNo;
        dat.SelectCommand.Parameters.Add("@u_id", SqlDbType.NVarChar, 60);
        dat.SelectCommand.Parameters["@u_id"].Value = UserID;

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 用于商品查询窗口获取数据
    /// </summary>
    /// <param name="UserID"></param>
    /// <returns>返回搜索数据集</returns>
    public DataSet SearchProductInfos(string UserID)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_QueryWinGetDataByProductInfo", con);

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;
        dat.SelectCommand.Parameters.Add("@u_log_id", SqlDbType.NVarChar, 60);
        dat.SelectCommand.Parameters["@u_log_id"].Value = UserID;

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 搜索商品资料信息
    /// </summary>
    /// <param name="Key">搜索关键字</param>
    /// <returns>返回搜索信息数据集</returns>
    public DataSet SearchProductDatas(string Key)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_SearchProductDatas", con);

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;
        dat.SelectCommand.Parameters.Add("@key", Key);

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 作废商品资料信息
    /// </summary>
    /// <param>商品资料编号</param>
    /// <param name="Is_Visible">作废状态1为临时作废，－1为彻底作废</param>
    /// <returns>返回作废是否成功</returns>
    public Boolean DeleteProductData(string ID, int Is_Visible)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_DeleteProductData", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@Im_no", SqlDbType.NVarChar, 20);
        com.Parameters["@Im_no"].Value = ID;
        com.Parameters.Add("@Is_Visible", SqlDbType.Int, 1);
        com.Parameters["@Is_Visible"].Value = Is_Visible;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 确认商品资料信息
    /// </summary>
    /// <param>商品资料编号</param>
    /// <param name="AuditStat">确认状态</param>
    /// <param name="Auditor">确认人</param>
    /// <returns>返回确认是否成功</returns>
    public Boolean AuditStatProductData(string ID, int AuditStat, string Auditor)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_AuditStatProductData", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@Im_no", SqlDbType.NVarChar, 20);
        com.Parameters["@Im_no"].Value = ID;
        com.Parameters.Add("@is_audit", SqlDbType.Int);
        com.Parameters["@is_audit"].Value = AuditStat;
        com.Parameters.Add("@auditor", SqlDbType.NVarChar, 60);
        com.Parameters["@auditor"].Value = Auditor;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 增加商品资料信息
    /// </summary>
    /// <param>商品资料编号</param>
    /// <param>......</param>
    /// <returns>返回修改是否成功</returns>
    public Boolean AddProductData(string sIm_no,string sIm_name,string sIm_sname,string sIt_Id,int iIs_mm,int iIs_effectdate,DateTime dIm_effectdate,
        string sIm_warnman,DateTime dIm_warndate,string sIm_number,decimal dcIm_buyprice,decimal dcCost_price,decimal dcIm_batprice,decimal dcPrice,decimal dcIm_agent1,
        decimal dcIm_agent2,decimal dcIm_agent3,string sNt_Id,string sIm_statistic1,string sIm_statistic2,string sIm_statistic3,string sIm_statistic4,string sIm_statistic5,					
        string sIm_ename,string sIm_factorycname,string sIm_factoryename,string sIm_mmtypenumber,string sIm_mmnumber,string sIm_size,string sIm_mctype,
        string sIm_parea, string sIm_makeup, string sIm_uses, string sIm_Ill, int iIs_otc, int iIs_batnumber, int iIs_IdentifyNo,string sOper, int iIs_stop, string sRemark)

        
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_AddProductData", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@Im_no", SqlDbType.NVarChar, 30);
        com.Parameters["@Im_no"].Value = sIm_no;
        com.Parameters.Add("@Im_name", SqlDbType.NVarChar, 60);
        com.Parameters["@Im_name"].Value = sIm_name;
        com.Parameters.Add("@Im_sname", SqlDbType.NVarChar, 60);
        com.Parameters["@Im_sname"].Value = sIm_sname;
        com.Parameters.Add("@It_Id", SqlDbType.NVarChar, 20);
        com.Parameters["@It_Id"].Value = sIt_Id;
        com.Parameters.Add("@Is_mm", SqlDbType.Int,1);
        com.Parameters["@Is_mm"].Value = iIs_mm;
        com.Parameters.Add("@Is_effectdate", SqlDbType.Int,1);
        com.Parameters["@Is_effectdate"].Value = iIs_effectdate;
        com.Parameters.Add("@Im_effectdate", SqlDbType.DateTime,8);
        com.Parameters["@Im_effectdate"].Value = dIm_effectdate;
        com.Parameters.Add("@Im_warnman", SqlDbType.NVarChar,1);
        com.Parameters["@Im_warnman"].Value = sIm_warnman;
        com.Parameters.Add("@Im_warndate", SqlDbType.DateTime,8);
        com.Parameters["@Im_warndate"].Value = dIm_warndate;
        com.Parameters.Add("@Im_number", SqlDbType.NVarChar, 30);
        com.Parameters["@Im_number"].Value = sIm_number;
        com.Parameters.Add("@Im_buyprice", SqlDbType.Decimal,17);
        com.Parameters["@Im_buyprice"].Value = dcIm_buyprice;
        com.Parameters.Add("@Cost_price", SqlDbType.Decimal, 17);
        com.Parameters["@Cost_price"].Value = dcCost_price;
        com.Parameters.Add("@Im_batprice", SqlDbType.Decimal,17);
        com.Parameters["@Im_batprice"].Value = dcIm_batprice;
        com.Parameters.Add("@Price", SqlDbType.Decimal,17);
        com.Parameters["@Price"].Value = dcPrice;
        com.Parameters.Add("@Im_agent1", SqlDbType.Decimal,17);
        com.Parameters["@Im_agent1"].Value = dcIm_agent1;
        com.Parameters.Add("@Im_agent2", SqlDbType.Decimal, 17);
        com.Parameters["@Im_agent2"].Value = dcIm_agent2;
        com.Parameters.Add("@Im_agent3", SqlDbType.Decimal,17);
        com.Parameters["@Im_agent3"].Value = dcIm_agent3;
        com.Parameters.Add("@Nt_Id", SqlDbType.NVarChar, 20);
        com.Parameters["@Nt_Id"].Value = sNt_Id;
        com.Parameters.Add("@Im_statistic1", SqlDbType.NVarChar, 20);
        com.Parameters["@Im_statistic1"].Value = sIm_statistic1;
        com.Parameters.Add("@Im_statistic2", SqlDbType.NVarChar, 20);
        com.Parameters["@Im_statistic2"].Value = sIm_statistic2;
        com.Parameters.Add("@Im_statistic3", SqlDbType.NVarChar, 20);
        com.Parameters["@Im_statistic3"].Value = sIm_statistic3;
        com.Parameters.Add("@Im_statistic4", SqlDbType.NVarChar, 20);
        com.Parameters["@Im_statistic4"].Value = sIm_statistic4;
        com.Parameters.Add("@Im_statistic5", SqlDbType.NVarChar, 20);
        com.Parameters["@Im_statistic5"].Value = sIm_statistic5;
        com.Parameters.Add("@Im_ename", SqlDbType.NVarChar,60);
        com.Parameters["@Im_ename"].Value = sIm_ename;
        com.Parameters.Add("@Im_factorycname", SqlDbType.NVarChar, 60);
        com.Parameters["@Im_factorycname"].Value = sIm_factorycname;
        com.Parameters.Add("@Im_factoryename", SqlDbType.NVarChar, 60);
        com.Parameters["@Im_factoryename"].Value = sIm_factoryename;
        com.Parameters.Add("@Im_mmtypenumber", SqlDbType.NVarChar, 20);
        com.Parameters["@Im_mmtypenumber"].Value = sIm_mmtypenumber;
        com.Parameters.Add("@Im_mmnumber", SqlDbType.NVarChar, 20);
        com.Parameters["@Im_mmnumber"].Value = sIm_mmnumber;
        com.Parameters.Add("@Im_size", SqlDbType.NVarChar, 60);
        com.Parameters["@Im_size"].Value = sIm_size;
        com.Parameters.Add("@Im_mctype", SqlDbType.NVarChar,60);
        com.Parameters["@Im_mctype"].Value = sIm_mctype;
        com.Parameters.Add("@Im_parea", SqlDbType.NVarChar,255);
        com.Parameters["@Im_parea"].Value = sIm_parea;
        com.Parameters.Add("@Im_makeup", SqlDbType.NVarChar,255);
        com.Parameters["@Im_makeup"].Value = sIm_makeup;
        com.Parameters.Add("@Im_uses", SqlDbType.NVarChar,255);
        com.Parameters["@Im_uses"].Value = sIm_uses;
        com.Parameters.Add("@Im_Ill", SqlDbType.NVarChar,255);
        com.Parameters["@Im_Ill"].Value = sIm_Ill;
        com.Parameters.Add("@Is_otc", SqlDbType.Int,1);
        com.Parameters["@Is_otc"].Value = iIs_otc;
        com.Parameters.Add("@Is_batnumber", SqlDbType.Int,1);
        com.Parameters["@Is_batnumber"].Value = iIs_batnumber;
        com.Parameters.Add("@Is_IdentifyNo", SqlDbType.Int, 1);//是否启用识别码
        com.Parameters["@Is_IdentifyNo"].Value = iIs_IdentifyNo;
        com.Parameters.Add("@Oper", SqlDbType.NVarChar, 60);
        com.Parameters["@Oper"].Value = sOper;
        com.Parameters.Add("@Is_stop", SqlDbType.Int,1);
        com.Parameters["@Is_stop"].Value = iIs_stop;
        com.Parameters.Add("@Remark", SqlDbType.NVarChar, 255);
        com.Parameters["@Remark"].Value = sRemark;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 修改商品资料信息
    /// </summary>
    /// <param>商品资料编号</param>
    /// <param>......</param>
    /// <returns>返回修改是否成功</returns>
    public Boolean UpDateProductData(string sIm_no,string sIm_name,string sIm_sname,string sIt_Id,int iIs_mm,int iIs_effectdate,DateTime dIm_effectdate,
        string sIm_warnman,DateTime dIm_warndate,string sIm_number,decimal dcIm_buyprice,decimal dcCost_price,decimal dcIm_batprice,decimal dcPrice,decimal dcIm_agent1,
        decimal dcIm_agent2,decimal dcIm_agent3,string sNt_Id,string sIm_statistic1,string sIm_statistic2,string sIm_statistic3,string sIm_statistic4,string sIm_statistic5,					
        string sIm_ename,string sIm_factorycname,string sIm_factoryename,string sIm_mmtypenumber,string sIm_mmnumber,string sIm_size,string sIm_mctype,
        string sIm_parea, string sIm_makeup, string sIm_uses, string sIm_Ill, int iIs_otc, int iIs_batnumber, int iIs_IdentifyNo, int iIs_stop, string sRemark)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_UpDateProductData", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@Im_no", SqlDbType.NVarChar, 30);
        com.Parameters["@Im_no"].Value = sIm_no;
        com.Parameters.Add("@Im_name", SqlDbType.NVarChar, 60);
        com.Parameters["@Im_name"].Value = sIm_name;
        com.Parameters.Add("@Im_sname", SqlDbType.NVarChar, 60);
        com.Parameters["@Im_sname"].Value = sIm_sname;
        com.Parameters.Add("@It_Id", SqlDbType.NVarChar, 20);
        com.Parameters["@It_Id"].Value = sIt_Id;
        com.Parameters.Add("@Is_mm", SqlDbType.Int, 1);
        com.Parameters["@Is_mm"].Value = iIs_mm;
        com.Parameters.Add("@Is_effectdate", SqlDbType.Int, 1);
        com.Parameters["@Is_effectdate"].Value = iIs_effectdate;
        com.Parameters.Add("@Im_effectdate", SqlDbType.DateTime, 8);
        com.Parameters["@Im_effectdate"].Value = dIm_effectdate;
        com.Parameters.Add("@Im_warnman", SqlDbType.NVarChar, 1);
        com.Parameters["@Im_warnman"].Value = sIm_warnman;
        com.Parameters.Add("@Im_warndate", SqlDbType.DateTime, 8);
        com.Parameters["@Im_warndate"].Value = dIm_warndate;
        com.Parameters.Add("@Im_number", SqlDbType.NVarChar, 30);
        com.Parameters["@Im_number"].Value = sIm_number;
        com.Parameters.Add("@Im_buyprice", SqlDbType.Decimal, 17);
        com.Parameters["@Im_buyprice"].Value = dcIm_buyprice;
        com.Parameters.Add("@Cost_price", SqlDbType.Decimal, 17);
        com.Parameters["@Cost_price"].Value = dcCost_price;
        com.Parameters.Add("@Im_batprice", SqlDbType.Decimal, 17);
        com.Parameters["@Im_batprice"].Value = dcIm_batprice;
        com.Parameters.Add("@Price", SqlDbType.Decimal, 17);
        com.Parameters["@Price"].Value = dcPrice;
        com.Parameters.Add("@Im_agent1", SqlDbType.Decimal, 17);
        com.Parameters["@Im_agent1"].Value = dcIm_agent1;
        com.Parameters.Add("@Im_agent2", SqlDbType.Decimal, 17);
        com.Parameters["@Im_agent2"].Value = dcIm_agent2;
        com.Parameters.Add("@Im_agent3", SqlDbType.Decimal, 17);
        com.Parameters["@Im_agent3"].Value = dcIm_agent3;
        com.Parameters.Add("@Nt_Id", SqlDbType.NVarChar, 20);
        com.Parameters["@Nt_Id"].Value = sNt_Id;
        com.Parameters.Add("@Im_statistic1", SqlDbType.NVarChar, 20);
        com.Parameters["@Im_statistic1"].Value = sIm_statistic1;
        com.Parameters.Add("@Im_statistic2", SqlDbType.NVarChar, 20);
        com.Parameters["@Im_statistic2"].Value = sIm_statistic2;
        com.Parameters.Add("@Im_statistic3", SqlDbType.NVarChar, 20);
        com.Parameters["@Im_statistic3"].Value = sIm_statistic3;
        com.Parameters.Add("@Im_statistic4", SqlDbType.NVarChar, 20);
        com.Parameters["@Im_statistic4"].Value = sIm_statistic4;
        com.Parameters.Add("@Im_statistic5", SqlDbType.NVarChar, 20);
        com.Parameters["@Im_statistic5"].Value = sIm_statistic5;
        com.Parameters.Add("@Im_ename", SqlDbType.NVarChar, 60);
        com.Parameters["@Im_ename"].Value = sIm_ename;
        com.Parameters.Add("@Im_factorycname", SqlDbType.NVarChar, 60);
        com.Parameters["@Im_factorycname"].Value = sIm_factorycname;
        com.Parameters.Add("@Im_factoryename", SqlDbType.NVarChar, 60);
        com.Parameters["@Im_factoryename"].Value = sIm_factoryename;
        com.Parameters.Add("@Im_mmtypenumber", SqlDbType.NVarChar, 20);
        com.Parameters["@Im_mmtypenumber"].Value = sIm_mmtypenumber;
        com.Parameters.Add("@Im_mmnumber", SqlDbType.NVarChar, 20);
        com.Parameters["@Im_mmnumber"].Value = sIm_mmnumber;
        com.Parameters.Add("@Im_size", SqlDbType.NVarChar, 60);
        com.Parameters["@Im_size"].Value = sIm_size;
        com.Parameters.Add("@Im_mctype", SqlDbType.NVarChar, 60);
        com.Parameters["@Im_mctype"].Value = sIm_mctype;
        com.Parameters.Add("@Im_parea", SqlDbType.NVarChar, 255);
        com.Parameters["@Im_parea"].Value = sIm_parea;
        com.Parameters.Add("@Im_makeup", SqlDbType.NVarChar, 255);
        com.Parameters["@Im_makeup"].Value = sIm_makeup;
        com.Parameters.Add("@Im_uses", SqlDbType.NVarChar, 255);
        com.Parameters["@Im_uses"].Value = sIm_uses;
        com.Parameters.Add("@Im_Ill", SqlDbType.NVarChar, 255);
        com.Parameters["@Im_Ill"].Value = sIm_Ill;
        com.Parameters.Add("@Is_otc", SqlDbType.Int, 1);
        com.Parameters["@Is_otc"].Value = iIs_otc;
        com.Parameters.Add("@Is_batnumber", SqlDbType.Int, 1);
        com.Parameters["@Is_batnumber"].Value = iIs_batnumber;
        com.Parameters.Add("@Is_IdentifyNo", SqlDbType.Int, 1);//是否启用识别码
        com.Parameters["@Is_IdentifyNo"].Value = iIs_IdentifyNo;
        //com.Parameters.Add("@Oper", SqlDbType.NVarChar, 60);
        //com.Parameters["@Oper"].Value = sOper;
        com.Parameters.Add("@Is_stop", SqlDbType.Int, 1);
        com.Parameters["@Is_stop"].Value = iIs_stop;
        com.Parameters.Add("@Remark", SqlDbType.NVarChar, 255);
        com.Parameters["@Remark"].Value = sRemark;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

}
